﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

using System.Data.OleDb;

namespace QuanLiHocSinh
{
    
    public partial class ThemMonHoc : Form
    {
        OleDbConnection connect;
        public ThemMonHoc()
        {
            InitializeComponent();

            //===========================================================================================


            connect = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=CSDL.mdb");

            if (connect != null)
            {
                connect.Open();

                //===========================
                string point = "select Mon from monhoc";
                OleDbCommand comman = new OleDbCommand(point, connect);

                OleDbDataReader reader;
                reader = comman.ExecuteReader();

                while (reader.Read())
                {
                    listBox1.Items.Add(reader.GetString(0));
                    comboBox1.Items.Add(reader.GetString(0));
                    comboBox2.Items.Add(reader.GetString(0));
                }

                string get_number_lop = "select SoMonHoc from rule";
                comman = new OleDbCommand(get_number_lop, connect);

                reader = comman.ExecuteReader();
                while (reader.Read())
                {
                    textBox5.Text = reader.GetString(0);
                }
                int num = 0;
                get_number_lop = "select count(*) from monhoc";
                comman = new OleDbCommand(get_number_lop, connect);

                reader = comman.ExecuteReader();
                while (reader.Read())
                {
                    num = reader.GetInt32(0);
                }

                textBox1.Text = num.ToString();


                //===========================
            }
            else
            {
                MessageBox.Show("Không kết nối được với CSDL", "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
            }



        }

        private void button2_Click(object sender, EventArgs e)
        {
            QuiDinh qd = new QuiDinh();
            qd.ShowDialog(this);
            qd.Activate();

            //===========================

            OleDbCommand comman;
            OleDbDataReader reader;

            string get_number_lop = "select SoMonHoc from rule";
            comman = new OleDbCommand(get_number_lop, connect);

            reader = comman.ExecuteReader();
            while (reader.Read())
            {
                textBox5.Text = reader.GetString(0);
            }
            int num = 0;
            get_number_lop = "select count(*) from monhoc";
            comman = new OleDbCommand(get_number_lop, connect);

            reader = comman.ExecuteReader();
            while (reader.Read())
            {
                num = reader.GetInt32(0);
            }

            textBox1.Text = num.ToString();


            //===========================

        }

        private void button1_Click(object sender, EventArgs e)
        {
            int hien_tai = int.Parse(textBox1.Text);
            int qui_dinh = int.Parse(textBox5.Text);
            if (hien_tai == qui_dinh)
            {
                MessageBox.Show("Số môn đã đủ!", "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
                return;
            }
            else
            {
                if (textBox3.Text == "")
                {
                    MessageBox.Show("Chưa nhập tên môn học!", "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
                    return;
                }
                else
                {
                    string point = "select * from monhoc";
                    OleDbCommand comman = new OleDbCommand(point, connect);

                    OleDbDataReader reader;
                    reader = comman.ExecuteReader();

                    while (reader.Read())
                    {
                        if (textBox3.Text == reader.GetString(0))
                        {
                            MessageBox.Show("Môn học đã tồn tại!", "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
                            return;
                        }
                    }

                    int num = 0;
                    string get_number_lop = "select count(*) from monhoc";
                    comman = new OleDbCommand(get_number_lop, connect);

                    reader = comman.ExecuteReader();
                    while (reader.Read())
                    {
                        num = reader.GetInt32(0);
                    }
                    num++;

                    point = "insert into monhoc(MaMon, Mon) values('"+num.ToString()+"', '" +textBox3.Text+ "')";
                    comman = new OleDbCommand(point, connect);

                    comman.ExecuteNonQuery();


                    MessageBox.Show("Thêm môn học thành công!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1);



                    //===========Tao bang diem mon=====================

                    string create = "create table " +num+ "(HocKy integer, HoTen char(255), Lop char(255),Diem15 decimal, Diem45 decimal, DiemHK decimal, DiemTB decimal, DatMon byte)";
                    comman = new OleDbCommand(create, connect);

                    comman.ExecuteNonQuery();

                    //=================================================

                    
                    //===========================
                    comboBox1.Items.Clear();
                    comboBox2.Items.Clear();
                    textBox3.ResetText();
                    point = "select Mon from monhoc";
                    comman = new OleDbCommand(point, connect);

                    
                    reader = comman.ExecuteReader();

                    while (reader.Read())
                    {
                        listBox1.Items.Add(reader.GetString(0));
                        comboBox1.Items.Add(reader.GetString(0));
                        comboBox2.Items.Add(reader.GetString(0));
                    }

                    get_number_lop = "select SoMonHoc from rule";
                    comman = new OleDbCommand(get_number_lop, connect);

                    reader = comman.ExecuteReader();
                    while (reader.Read())
                    {
                        textBox5.Text = reader.GetString(0);
                        
                    }
                    num = 0;
                    get_number_lop = "select count(*) from monhoc";
                    comman = new OleDbCommand(get_number_lop, connect);

                    reader = comman.ExecuteReader();
                    while (reader.Read())
                    {
                        num = reader.GetInt32(0);
                    }

                    textBox1.Text = num.ToString();


                    //===========================

                }
            }
        }

        private void button5_Click(object sender, EventArgs e)
        {
            if (comboBox1.Text == "")
            {
                MessageBox.Show("Chưa chọn môn học!", "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
            }
            else
            {
                if (MessageBox.Show("Nếu xóa môn học thì toàn bộ dữ liệu của môn học sẽ bị xóa!!!", "Thông báo!", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button2) == DialogResult.OK)
                {
                    //===========Xoa bang diem mon=====================
                    string ten_bang_mon = "";
                    string get = "select MaMon from monhoc where Mon = '"+comboBox1.Text+"'";
                    OleDbCommand comman = new OleDbCommand(get, connect);
                    OleDbDataReader reader = comman.ExecuteReader();
                    while (reader.Read())
                    {
                        ten_bang_mon = reader.GetString(0);
                    }

                    reader.Close();
                    string create = "drop table " + ten_bang_mon + "";
                    comman = new OleDbCommand(create, connect);

                    comman.ExecuteNonQuery();

                    //=================================================

                    string del = "delete * from monhoc where Mon = '" + comboBox1.Text + "'";
                    comman = new OleDbCommand(del, connect);
                    comman.ExecuteNonQuery();

                    MessageBox.Show("Xóa môn học thành công!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1);

                    



                    //===========================
                    comboBox1.Items.Clear();
                    comboBox2.Items.Clear();
                    comboBox1.ResetText();
                    string point = "select Mon from monhoc";
                    comman = new OleDbCommand(point, connect);

                    
                    reader = comman.ExecuteReader();

                    while (reader.Read())
                    {
                        listBox1.Items.Add(reader.GetString(0));
                        comboBox1.Items.Add(reader.GetString(0));
                        comboBox2.Items.Add(reader.GetString(0));
                    }

                    string get_number_lop = "select SoMonHoc from rule";
                    comman = new OleDbCommand(get_number_lop, connect);

                    reader = comman.ExecuteReader();
                    while (reader.Read())
                    {
                        textBox5.Text = reader.GetString(0);

                    }
                    int num = 0;
                    get_number_lop = "select count(*) from monhoc";
                    comman = new OleDbCommand(get_number_lop, connect);

                    reader = comman.ExecuteReader();
                    while (reader.Read())
                    {
                        num = reader.GetInt32(0);
                    }

                    textBox1.Text = num.ToString();


                    //===========================
                }
            }
        }

        private void button4_Click(object sender, EventArgs e)
        {
            if ((comboBox2.Text == "")||(textBox4.Text == ""))
            {
                MessageBox.Show("Chưa chọn hoặc chưa nhập tên môn học!", "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
            }
            else
            {
                
                string point = "update monhoc set Mon = '"+textBox4.Text+"' where Mon = '"+comboBox2.Text+"'";
                OleDbCommand comman = new OleDbCommand(point, connect);

                comman.ExecuteNonQuery();


                MessageBox.Show("Thay đổi môn học thành công!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1);


            



                //===========================
                comboBox1.Items.Clear();
                comboBox2.Items.Clear();
                comboBox2.ResetText();
                textBox4.ResetText();
                
                point = "select Mon from monhoc";
                comman = new OleDbCommand(point, connect);

                
                OleDbDataReader reader = comman.ExecuteReader();

                while (reader.Read())
                {
                    listBox1.Items.Add(reader.GetString(0));
                    comboBox1.Items.Add(reader.GetString(0));
                    comboBox2.Items.Add(reader.GetString(0));
                }

                string get_number_lop = "select SoMonHoc from rule";
                comman = new OleDbCommand(get_number_lop, connect);

                reader = comman.ExecuteReader();
                while (reader.Read())
                {
                    textBox5.Text = reader.GetString(0);

                }
                int num = 0;
                get_number_lop = "select count(*) from monhoc";
                comman = new OleDbCommand(get_number_lop, connect);

                reader = comman.ExecuteReader();
                while (reader.Read())
                {
                    num = reader.GetInt32(0);
                }

                textBox1.Text = num.ToString();


                //===========================
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        




    }
}
